package ch.il07.converter.persistence.impl.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import ch.il07.converter.persistence.entities.AccessLevel;
import ch.il07.converter.persistence.entities.Measurand;
import ch.il07.converter.persistence.entities.Unit;
import ch.il07.converter.persistence.entities.User;
import ch.il07.converter.persistence.exceptions.AlreadyDefinedException;
import ch.il07.converter.persistence.exceptions.NoSuchMeasurandException;
import ch.il07.converter.persistence.exceptions.NoSuchUnitException;
import ch.il07.converter.persistence.exceptions.NoSuchUserException;
import ch.il07.converter.persistence.exceptions.PersistenceException;
import ch.il07.converter.persistence.exceptions.UpdateException;
import ch.il07.converter.persistence.exceptions.UserAlreadyExistsException;
import ch.il07.converter.persistence.impl.mysql.pooling.DbPoolPairManager;

public class MySQLPersistence extends AbstractMySQLPersistence {

	public MySQLPersistence() {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		manager.addPool(MYSQL_POOL, getMySQLPool());
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Measurand> getMeasurands() throws PersistenceException {
		return getMeasurands(null);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Measurand> getMeasurands(String username)
			throws NoSuchUserException, PersistenceException {

		User user = null;
		if (username != null) {
			user = getUser(username);
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT measurandID, "
							+ "name, description FROM measurand m WHERE "
							+ createUserIDStmt(user));
			List<Measurand> measurands = new ArrayList<Measurand>();
			ResultSet res = stmt.executeQuery();
			while (res.next()) {
				Measurand m = new Measurand(res.getString(2), null);
				m.setMeasurandId(res.getInt(1));
				m.setDescription(res.getString(3));
				m.setDefaultUnit(getDefaultUnitForMeasurand(m));

				// user will perhaps be null, but that's o.k. ;-)
				m.setOwner(user);
				m.setUnits(getUnitsForMeasurand(m, user));

				measurands.add(m);
			}
			return measurands;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * @desc reads the default unit from the database
	 * 
	 * @param Measurand
	 *            m
	 * 
	 * @return the default Unit for a specific Measurand (identified by
	 *         measurandID!!)
	 * 
	 * @throws PersistenceException
	 *             on database error
	 * @throws IllegalArgumentException
	 *             if the measurandID is null
	 */
	private Unit getDefaultUnitForMeasurand(Measurand m)
			throws PersistenceException {
		if (m.getMeasurandID() == null) {
			throw new IllegalArgumentException("measurandID must not be null");
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT "
							+ "unitID, name, userID, symbol FROM unit WHERE factor = 1 AND shift = 0 AND measurandID = ?");
			stmt.setInt(1, m.getMeasurandID());
			ResultSet res = stmt.executeQuery();
			if (res.next()) {
				Unit unit = new Unit(res.getString(2), 1D, 0D);
				unit.setUnitId(res.getInt(1));
				unit.setOwner(getUserById(res.getInt(3)));
				unit.setSymbol(res.getString(4));
				return unit;
			} else {
				throw new IllegalStateException("Every Measurand must have a default unit, The database is inconsistent!");
			}
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * 
	 * @param measurand
	 * @param user
	 * @return any unit but the default one
	 * @throws PersistenceException
	 * @throws NoSuchMeasurandException
	 */
	private List<Unit> getUnitsForMeasurand(Measurand measurand, User user)
			throws PersistenceException, NoSuchMeasurandException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con.prepareStatement("SELECT unitID, "
					+ "userID, name, factor, shift, symbol FROM unit WHERE "
					+ "(factor != 1 OR shift != 0) AND measurandID = ?");
			stmt.setInt(1, measurand.getMeasurandID());
			ResultSet res = stmt.executeQuery();

			List<Unit> result = new ArrayList<Unit>();
			while (res.next()) {
				Unit u = new Unit(res.getString(3), res.getDouble(4),
						res.getDouble(5), getUserById(res.getInt(2)));
				u.setUnitId(res.getInt(1));
				u.setSymbol(res.getString(6));
				result.add(u);
			}
			return result;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public Measurand getMeasurand(String measurandName, String username)
			throws NoSuchUserException, PersistenceException {
		User user = null;
		if (username != null) {
			user = getUser(username);
			if (user == null) {
				throw new NoSuchUserException("user with username " + username
						+ " does not exist");
			}
		}

		Connection con = DbPoolPairManager.getInstance().getConnection(
				MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT measurandID, "
							+ "name, description FROM measurand WHERE name = ? AND "
							+ createUserIDStmt(user));
			stmt.setString(1, measurandName);
			ResultSet res = stmt.executeQuery();
			if (res.next()) {
				Measurand m = new Measurand(measurandName, null);
				m.setMeasurandId(res.getInt(1));
				m.setName(measurandName);
				m.setDescription(res.getString(3));
				m.setDefaultUnit(getDefaultUnitForMeasurand(m));
				m.setOwner(user);
				m.setUnits(getUnitsForMeasurand(m, user));
				return m;
			} else {
				return null;
			}
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				DbPoolPairManager.getInstance().putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public User getUser(String username, String password)
			throws PersistenceException {
		User user = getUser(username);

		if (user == null) {
			return null;
		}

		if (user.getPassword().equals(md5(password))) {
			user.setPassword(null); // don't show the password to the ugly
									// business
			return user;
		}
		// password is wrong
		return null;
	}

	/**
	 * 
	 * @param username
	 * 
	 * @return user object, if user exists. null if username doesn't exist
	 * 
	 * @throws PersistanceException
	 *             on Database error
	 */
	@Override
	public User getUser(String username) throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT userID, username, "
							+ "password, email, administrator FROM user WHERE username = ?");
			stmt.setString(1, username);
			ResultSet res = stmt.executeQuery();
			if (res.next()) {
				return new User(res.getInt(1), res.getString(2),
						res.getString(3), res.getString(4),
						AccessLevel.getAccessLevelById(res.getInt(5)));
			} else {
				return null;
			}
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	private User getUserById(Integer userID) throws PersistenceException {
		if (userID == null) {
			throw new IllegalArgumentException("userID must not be null");
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT userID, "
							+ "username, password, email, administrator FROM user WHERE userID = ?");
			stmt.setInt(1, userID);
			ResultSet res = stmt.executeQuery();
			if (res.next()) {
				return new User(res.getInt(1), res.getString(2),
						res.getString(4), AccessLevel.getAccessLevelById(res
								.getInt(5)));
			} else {
				return null; // no such user
			}
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void addMeasurand(Measurand measurand, String username)
			throws NoSuchUserException, AlreadyDefinedException,
			PersistenceException {
		Unit defaultUnit = measurand.getDefaultUnit();
		if (defaultUnit == null) {
			throw new IllegalArgumentException("default unit must be set");
		} else if (defaultUnit.getDivisor() != 1 || defaultUnit.getShift() != 0) {
			throw new IllegalArgumentException(
					"defaultUnit: divisor must be 1 and shift must be 0");
		} else if (measurand.getName() == null) {
			throw new IllegalArgumentException("Owner must be set");
		}

		// get the userID out of the database.
		// null if the current measurand has no owner
		Integer userID = null;
		if (username != null) {
			User user = getUser(username);
			userID = user.getUserID();
			if (measurand.getOwner() == null) {
				measurand.setOwner(user);
				defaultUnit.setOwner(user);
				for(Unit u : measurand.getUnits()) {
					u.setOwner(user);
				}
			}
		}

		if (-1 != measurandExists(measurand.getName())) {
			throw new AlreadyDefinedException("Measurand ["
					+ measurand.getName() + "] does already exist!");
		}

		Connection con = DbPoolPairManager.getInstance().getConnection(
				MYSQL_POOL);
		try {
			PreparedStatement insertStmt = con.prepareStatement("INSERT INTO "
					+ "measurand (name, description, userID) VALUES (?, ?, ?)");
			insertStmt.setString(1, measurand.getName());
			insertStmt.setString(2, measurand.getDescription());
			if (userID == null) {
				insertStmt.setNull(3, Types.INTEGER);
			} else {
				insertStmt.setInt(3, userID);
			}
			insertStmt.executeUpdate();

			// get the inserted id for the measurand
			PreparedStatement selectStmt = con
					.prepareStatement("SELECT "
							+ "measurandID FROM measurand WHERE name = ? ORDER BY measurandID DESC LIMIT 1");
			selectStmt.setString(1, measurand.getName());
			ResultSet res = selectStmt.executeQuery();
			res.next();
			measurand.setMeasurandId(res.getInt("measurandID"));
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				DbPoolPairManager.getInstance().putConnection(MYSQL_POOL, con);
			}
		}

		addUnit(measurand, measurand.getDefaultUnit());
		for (Unit unit : measurand.getUnits()) {
			addUnit(measurand, unit);
		}
	}

	private static boolean measurandExists(Integer measurandID)
			throws PersistenceException {
		if (measurandID == null) {
			throw new IllegalArgumentException("measurandID must not be null");
		}
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {

			PreparedStatement stmt = con
					.prepareStatement("SELECT measurandID FROM measurand WHERE measurandID = ?");
			stmt.setInt(1, measurandID);
			ResultSet rs = stmt.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	private static int measurandExists(String name) throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {

			PreparedStatement stmt = con
					.prepareStatement("SELECT measurandID FROM measurand WHERE name = ?");
			stmt.setString(1, name);

			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
			return -1;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void addUnit(Measurand measurand, Unit unit)
			throws AlreadyDefinedException, PersistenceException {
		Integer userID = null;
		User user = unit.getOwner();
		if (unitExists(measurand, unit)) {
			throw new AlreadyDefinedException("Unit is already defined");
		} else if (user != null && user.getAccessLevel() == AccessLevel.USER) {
			userID = unit.getOwner().getUserID();
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con.prepareStatement("INSERT INTO unit ("
					+ "   measurandID, userID, name, factor, shift, symbol"
					+ ") " + "VALUES (?, ?, ?, ?, ?, ?)");
			stmt.setInt(1, measurand.getMeasurandID());
			if (userID == null) {
				stmt.setNull(2, Types.INTEGER);
			} else {
				stmt.setInt(2, userID);
			}
			if (unit.getSymbol() == null) {
				stmt.setNull(6, Types.VARCHAR);
			} else {
				stmt.setString(6, unit.getSymbol());
			}
			stmt.setString(3, unit.getName());
			stmt.setDouble(4, unit.getDivisor());
			stmt.setDouble(5, unit.getShift());
			stmt.executeUpdate();

			// get the inserted id for the unit
			PreparedStatement selectStmt = con
					.prepareStatement("SELECT "
							+ "unitID FROM unit WHERE name = ? ORDER BY unitID DESC LIMIT 1");
			selectStmt.setString(1, unit.getName());
			ResultSet res = selectStmt.executeQuery();
			res.next();
			unit.setUnitId(res.getInt("unitID"));
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	private boolean unitExists(Measurand measurand, Unit unit)
			throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt;

			if (unit.getUnitId() == null) {
				stmt = con
						.prepareStatement("SELECT m.measurandID, u.unitID FROM measurand m "
								+ "JOIN unit u USING(measurandID) WHERE m.name = ? AND u.name = ?");
				stmt.setString(1, measurand.getName());
				stmt.setString(2, unit.getName());
			} else {
				stmt = con
						.prepareStatement("SELECT m.measurandID, u.unitID FROM measurand m "
								+ "JOIN unit u USING(measurandID) WHERE u.unitID = ?");
				stmt.setInt(1, unit.getUnitId());
			}
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				if (measurand.getMeasurandID() == null) {
					measurand.setMeasurandId(rs.getInt(1));
				}
				if (unit.getUnitId() == null) {
					unit.setUnitId(rs.getInt(2));
				}
				return true;
			}
			return false;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void addUser(String username, String email, String password,
			AccessLevel accessLevel) throws UserAlreadyExistsException,
			PersistenceException {
		if (getUser(username) != null) {
			throw new UserAlreadyExistsException("User '" + username
					+ "' already exist");
		}
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("INSERT INTO "
							+ "user(username, email, password, administrator) VALUES(?, ?, md5(?) ,?)");
			stmt.setString(1, username);
			stmt.setString(2, email);
			stmt.setString(3, password);
			stmt.setShort(4, accessLevel.getAccessLevelId().shortValue());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void updateMeasurand(Measurand measurand)
			throws NoSuchUserException, PersistenceException {
		if (measurand.getMeasurandID() == null) {
			throw new IllegalArgumentException("The measurandID has to be set!");
		} else if (!measurandExists(measurand.getMeasurandID())) {
			throw new NoSuchMeasurandException("measurand: " + measurand);
		} else if (measurandNameAlreadExists(measurand.getMeasurandID(),
				measurand.getName())) {
			throw new UpdateException("measurand with name ["
					+ measurand.getName() + "] already exists");
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con
					.prepareStatement("UPDATE measurand SET "
							+ "name = ?, description = ?, userID = ? WHERE measurandID = ?");
			stmt.setString(1, measurand.getName());
			if (measurand.getDescription() == null) {
				stmt.setNull(2, Types.VARCHAR);
			} else {
				stmt.setString(2, measurand.getDescription());
			}
			if (measurand.getOwner() == null) {
				stmt.setNull(3, Types.INTEGER);
			} else {
				stmt.setInt(3, measurand.getOwner().getUserID());
			}
			stmt.setInt(4, measurand.getMeasurandID());
			stmt.execute();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	private static boolean measurandNameAlreadExists(Integer measurandID,
			String name) throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT measurandID FROM measurand WHERE measurandID != ? AND name = ?");
			stmt.setInt(1, measurandID);
			stmt.setString(2, name);
			ResultSet res = stmt.executeQuery();
			return res.next();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void updateUnit(Measurand measurand, Unit unit)
			throws PersistenceException {
		if (unit.getUnitId() == null) {
			throw new IllegalArgumentException("unit id must be set to update");
		} else if (!unitExists(measurand, unit)) {
			throw new NoSuchUnitException("unit: " + unit);
		} else if (unitNameAlreadyExists(measurand.getMeasurandID(), unit.getUnitId(), unit.getName())) {
			throw new UpdateException("unit with name [" + unit.getName()
					+ "] already exists");
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con
					.prepareStatement("UPDATE unit SET "
							+ "userID = ?, symbol = ?, name = ?, factor = ?, shift = ? WHERE unitID = ?");
			if (unit.getOwner() == null) {
				stmt.setNull(1, Types.INTEGER);
			} else {
				stmt.setInt(1, unit.getOwner().getUserID());
			}
			if (unit.getSymbol() == null) {
				stmt.setNull(2, Types.VARCHAR);
			} else {
				stmt.setString(2, unit.getSymbol());
			}
			stmt.setString(3, unit.getName());
			stmt.setDouble(4, unit.getDivisor());
			stmt.setDouble(5, unit.getShift());
			stmt.setInt(6, unit.getUnitId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * 
	 * @param measurandID
	 * @param unitID
	 * @param name
	 * @return true if the given measurand doesn't have a unit with the same name yet
	 * @throws PersistenceException
	 */
	private static boolean unitNameAlreadyExists(Integer measurandID, Integer unitID, String name)
			throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT unitID FROM unit WHERE measurandID = ? AND unitID != ? AND name = ?");
			stmt.setInt(1, measurandID);
			stmt.setInt(2, unitID);
			stmt.setString(3, name);
			ResultSet res = stmt.executeQuery();
			return res.next();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void updateUser(User user) throws PersistenceException {
		if (user.getUserID() == null) {
			throw new IllegalArgumentException("userID must not be null");
		} else if (getUserById(user.getUserID()) == null) {
			throw new NoSuchUserException("user: " + user);
		} else if (userNameAlreadyExists(user.getUserID(), user.getUsername())) {
			throw new UpdateException("user with username ["
					+ user.getUsername() + "] already exists");
		}

		boolean passwordHasToBeChanged = (user.getPassword() != null);

		StringBuilder sql = new StringBuilder(
				"UPDATE user SET username=?, email=?, administrator=?");
		if (passwordHasToBeChanged) {
			sql.append(", password=md5(?)");
		}
		sql.append("WHERE userID=?");

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con.prepareStatement(sql.toString());
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getEmail());
			stmt.setInt(3, user.getAccessLevel().getAccessLevelId());
			if (passwordHasToBeChanged) {
				stmt.setString(4, user.getPassword());
				stmt.setInt(5, user.getUserID());
			} else {
				stmt.setInt(4, user.getUserID());
			}
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	private boolean userNameAlreadyExists(Integer userID, String username)
			throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			PreparedStatement stmt = con
					.prepareStatement("SELECT userID FROM user WHERE userID != ? AND username = ?");
			stmt.setInt(1, userID);
			stmt.setString(2, username);
			ResultSet res = stmt.executeQuery();
			return res.next();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void nominate(User user, Measurand measurand)
			throws PersistenceException {
		if (measurand.getMeasurandID() == null) {
			throw new IllegalArgumentException("Measurand ID must be set");
		}

		Integer userid = measurand.getOwner().getUserID();
		Integer mid = measurand.getMeasurandID();

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("INSERT INTO nomination (userID,measurandID)VALUES(?, ?)");
			stmt.setInt(1, userid);
			stmt.setInt(2, mid);
			stmt.execute();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void nominate(User user, Measurand measurand, Unit unit)
			throws PersistenceException {
		if (user.getUserID() == null) {
			throw new IllegalArgumentException("User ID must no be null");
		} else if (!measurandExists(measurand.getMeasurandID())) {
			throw new NoSuchMeasurandException("name: " + measurand.getName());
		} else if (!unitExists(measurand, unit)) {
			throw new NoSuchUnitException("name: " + unit.getName());
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			PreparedStatement stmt = con
					.prepareStatement("INSERT INTO nomination VALUES(?, ?, ?);");
			stmt.setInt(1, user.getUserID());
			stmt.setInt(2, measurand.getMeasurandID());
			stmt.setInt(3, unit.getUnitId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void deleteMeasurand(Measurand measurand, String username)
			throws NoSuchUserException, PersistenceException {
		deleteUnit(measurand, measurand.getDefaultUnit());
		for (Unit u : measurand.getUnits()) {
			deleteUnit(measurand, u);
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			String sql = "DELETE FROM measurand WHERE name = ? ";
			boolean isPublicUnit = (username == null);
			if (isPublicUnit) {
				sql += "AND userID IS NULL;";
			} else {
				sql += "AND userID = (SELECT u.userID FROM user u WHERE u.username = ?) ;";
			}
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, measurand.getName());
			if (!isPublicUnit) {
				stmt.setString(2, username);
			}
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void deleteUnit(Measurand measurand, Unit unit)
			throws NoSuchUnitException, NoSuchMeasurandException,
			PersistenceException {
		if (!measurandExists(measurand.getMeasurandID())) {
			throw new NoSuchMeasurandException("measurand "
					+ measurand.getName() + " does not exists");
		} else if (!unitExists(measurand, unit)) {
			throw new NoSuchUnitException("unit " + unit.getName()
					+ " does not exists");
		}

		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			User user = measurand.getOwner();
			PreparedStatement stmt = con.prepareStatement("DELETE FROM unit "
					+ "WHERE name = ? AND measurandID = ? AND "
					+ createUserIDStmt(user));
			stmt.setString(1, unit.getName());
			stmt.setInt(2, measurand.getMeasurandID());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void deleteUser(User user) throws PersistenceException {
		// will also delete corresponding units
		List<Measurand> allMeasurands = getMeasurands(user.getUsername());
		for (Measurand m : allMeasurands) {
			deleteMeasurand(m, user.getUsername());
		}
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);
		try {
			String sql = "DELETE FROM user WHERE username = ? ";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, user.getUsername());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Measurand> getAllNominatedMeasurands()
			throws PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		List<Measurand> measurands = new ArrayList<Measurand>();
		try {
			String sql = "SELECT m.measurandID, name, description, m.userID  FROM measurand m INNER JOIN nomination n ON n.measurandID=m.measurandID where n.unitID is null";
			ResultSet res = con.createStatement().executeQuery(sql);
			while (res.next()) {
				Measurand m = new Measurand(res.getString(2), null);

				m.setMeasurandId(res.getInt("measurandID"));
				m.setName(res.getString("name"));
				m.setDescription(res.getString("description"));
				m.setOwner(getUserById(res.getInt("userID")));

				measurands.add(m);
			}

			return measurands;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Unit> getAllNominatedUnits() throws NoSuchMeasurandException,
			PersistenceException {
		DbPoolPairManager manager = DbPoolPairManager.getInstance();
		Connection con = manager.getConnection(MYSQL_POOL);

		try {
			List<Unit> listOfAllNominatedUnits = new ArrayList<Unit>();
			String sql = "SELECT name, factor, shift, unit.userID, unit.unitID FROM nomination INNER JOIN unit USING (unitID) where unitID is not NULL;";
			ResultSet res = con.createStatement().executeQuery(sql);
			while (res.next()) {
				User owner = getUserById(res.getInt(4));
				Unit u = new Unit(res.getString(1), res.getDouble(2),
						res.getDouble(3), owner);
				u.setUnitId(res.getInt(5));
				listOfAllNominatedUnits.add(u);
			}
			return listOfAllNominatedUnits;
		} catch (SQLException e) {
			throw new PersistenceException(e);
		} finally {
			if (con != null) {
				manager.putConnection(MYSQL_POOL, con);
			}
		}
	}
}